CREATE OR REPLACE PROCEDURE personne_to_dimission_timer
IS
BEGIN

    -- 定薪人员超时转离职
    MERGE INTO p_form_employ_apply_d chgd
    USING (
        SELECT * FROM p_form_employ_apply_d dd
        LEFT JOIN p_form_employ_apply dm ON dd.pphid = dm.phid
        WHERE dm.u_ht_enddt<=SYSDATE
        AND NOT EXISTS ( SELECT 1 FROM p_form_employ_resignation_apply lz WHERE lz.u_hr_no = dd.u_ry_no )
    ) dx ON (dx.u_ry_no = chgd.u_ry_no)
    WHEN MATCHED THEN
        UPDATE SET
            chgd.u_real_dt = dx.u_ht_enddt,
            chgd.u_ry_zt = '35';


    MERGE INTO hr_epm_main hr
    USING (
        SELECT * FROM p_form_employ_apply_d dd
        LEFT JOIN p_form_employ_apply dm ON dd.pphid = dm.phid
        WHERE dm.u_ht_enddt<=SYSDATE
        AND NOT EXISTS ( SELECT 1 FROM p_form_employ_resignation_apply lz WHERE lz.u_hr_no = dd.u_ry_no )
    ) dx ON (dx.u_ry_no = hr.bill_no)
    WHEN MATCHED THEN
        UPDATE SET
            hr.empstatus = '35';


    MERGE INTO fg3_user us
    USING (
        SELECT * FROM p_form_employ_apply_d dd
        LEFT JOIN p_form_employ_apply dm ON dd.pphid = dm.phid
        WHERE dm.u_ht_enddt<=SYSDATE
        AND NOT EXISTS ( SELECT 1 FROM p_form_employ_resignation_apply lz WHERE lz.u_hr_no = dd.u_ry_no )
    ) dx ON (dx.u_ry_no = us.userno)
    WHEN MATCHED THEN
        UPDATE SET
            us.status = '2';

    -- 研发用工合同人员调动
    MERGE INTO pcm3_cnt_d chgd
    USING (
        SELECT * FROM pcm3_cnt_d dd
        LEFT JOIN pcm3_cnt_m dm ON dd.pphid = dm.phid
        WHERE dm.user_ht_enddt <= SYSDATE
        AND NOT EXISTS ( SELECT 1 FROM p_form_employ_resignation_apply lz WHERE lz.u_hr_no = dd.item_no )
    ) cnt ON (cnt.item_no = chgd.item_no)
    WHEN MATCHED THEN
        UPDATE SET
            chgd.user_real_dt = cnt.user_ht_enddt,
            chgd.user_ry_zt = '35';

    -- 提交事务
    COMMIT;

EXCEPTION
    -- 捕捉所有类型的异常
    WHEN OTHERS THEN
        -- 进行事务回滚
        ROLLBACK;
        -- 使用RAISE_APPLICATION_ERROR函数抛出一个自定义错误
        -- 第一个参数是错误代码（范围是-20000到-20999），第二个参数是错误消息
        RAISE_APPLICATION_ERROR(-20001, 'An error occurred in the procedure: ' || SQLERRM);

END;




BEGIN
  DBMS_SCHEDULER.create_job(
    job_name        => 'update_actual_end_date_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN update_actual_end_date; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=7; BYMINUTE=0; BYSECOND=0',
    enabled         => TRUE
  );
END;
/
